import openpyxl
import datetime
import time
import os
import sys
import logging
import configparser
import json
import ChengBanBuMen
import ReplaceBuMen
from hanchlogger import logger
from openpyxl.cell import MergedCell
from openpyxl.styles import Alignment, Font
from openpyxl.utils import get_column_letter
import numpy as np
import pandas as pd
import hanchxl
import docx
import random

# import GenerateCharts
# from docx.shared import RGBColor
# from docxcompose.composer import Composer

timeCur = datetime.datetime.now()
# dirPath = os.path.dirname(os.path.realpath(sys.executable))
dirPath ='E:\\日常工作\\剔除组工作\\剔除组分组'

logFile = dirPath + '\\TiChuFenZu.log'
logger.get_log(logFile, 'LoggerTiChuFenZu', logging.DEBUG)

numFenZu = 9

timeYesterdayStr = datetime.datetime.fromtimestamp(timeCur.timestamp() - 60 * 60 * 24).strftime('%Y-%m-%d')
timeYesterdayStr = timeYesterdayStr + '  {}:{}:{}'.format(timeCur.hour, timeCur.minute, timeCur.second)
datatimeYesterday = datetime.datetime.strptime(timeYesterdayStr, '%Y-%m-%d %H:%M:%S')
timeYesterday = time.strptime(timeYesterdayStr, '%Y-%m-%d %H:%M:%S')
timeYesterdayStamp = int(time.mktime(timeYesterday))
logger.debug('昨天（日期）：{}    昨天（时间戳）：{}'.format(timeYesterdayStr, timeYesterdayStamp))

fileResultFenZu = dirPath + '\\剔除组分组.xlsx'
fileGongDanQuanBu = dirPath + '\\全部工单.xlsx'

# 打开'剔除组分组'文件
bookResultFenZu = openpyxl.Workbook()
sheetResultFenZu = bookResultFenZu.active

# 打开'全部工单'文件
bookQuanBuGongDan = openpyxl.load_workbook(fileGongDanQuanBu, data_only=True)
sheetQuanBuGongDan = bookQuanBuGongDan.active
maxRowQuanBuGongDan = sheetQuanBuGongDan.max_row
maxColQuanBuGongDan = sheetQuanBuGongDan.max_column
logger.debug('全部工单：行数 ：{}'.format(maxRowQuanBuGongDan))  # 打印表的行数
logger.debug('全部工单：列数 ：{}'.format(maxColQuanBuGongDan))  # 打印表的列数
colChengBan = hanchxl.get_col_index(sheetQuanBuGongDan, '承办部门')
listChengBanBuMeInfo = {}

for row in range(2, maxRowQuanBuGongDan + 1):
    valueChengBan = '{}'.format(sheetQuanBuGongDan.cell(row, colChengBan).value).strip()
    if valueChengBan in listChengBanBuMeInfo:
        listChengBanBuMeInfo[valueChengBan] = listChengBanBuMeInfo[valueChengBan] + 1
    else:
        listChengBanBuMeInfo[valueChengBan] = 1


logger.debug('承办部门（排序前）：{}'.format(listChengBanBuMeInfo))  # 打印所有承办部门
listChengBanBuMeInfo = dict(sorted(listChengBanBuMeInfo.items(), key=lambda kv: kv[1], reverse=True))
logger.debug('承办部门（排序后）：{}'.format(listChengBanBuMeInfo))  # 打印所有承办部门


class MemberInFo(object):
    def __init__(self):
        self.count = 0
        self.listChengBan = {}


dictMemberFenZu = {}
# listMember = ['刘杰', '赵思安', '王珊', '郑希', '孔令华', '常少龙', '宋琼', '林思学', '马丹丹', '韩冲']
listMember = ['刘杰', '赵思安', '王珊', '郑希', '孔令华', '常少龙', '宋琼', '林思学', '马丹丹', '李倩', '沈思', '周晓兰', '李苗']
for index in listMember:
    memberInFo = MemberInFo()
    dictMemberFenZu[index] = memberInFo

for indexBuMe in listChengBanBuMeInfo:
    key, value = dictMemberFenZu.popitem()
    logger.debug('Key：{}   value.count：{} value.listChengBan:{}'.format(key, value.count, value.listChengBan))
    value.count = value.count + listChengBanBuMeInfo[indexBuMe]
    value.listChengBan[indexBuMe] = listChengBanBuMeInfo[indexBuMe]
    dictMemberFenZu[key] = value
    dictMemberFenZu = dict(sorted(dictMemberFenZu.items(), key=lambda kv: kv[1].count, reverse=True))

for index in dictMemberFenZu:
    logger.debug('{}：总件数：{}  负责部门：{}'.format(index, dictMemberFenZu[index].count, dictMemberFenZu[index].listChengBan))

insertCol = 1
for indexFenZu in dictMemberFenZu:
    insetRow = 1
    sheetResultFenZu.cell(insetRow, insertCol).value = indexFenZu
    sheetResultFenZu.cell(insetRow, insertCol + 1).value = dictMemberFenZu[indexFenZu].count
    insetRow = insetRow + 1
    for indexChengBan in dictMemberFenZu[indexFenZu].listChengBan:
        insetRow = insetRow + 1
        sheetResultFenZu.cell(insetRow, insertCol).value = indexChengBan
        sheetResultFenZu.cell(insetRow, insertCol + 1).value = dictMemberFenZu[indexFenZu].listChengBan[indexChengBan]
    insertCol = insertCol + 3


bookResultFenZu.save(fileResultFenZu)
